//
//  DBHelper.m
//  fmdbDemo
//
//  Created by rp.wang on 2017/9/21.
//  Copyright © 2017年 rp.wang. All rights reserved.
//

#import "DBHelper.h"

@interface DBHelper ()

@property (nonatomic, strong) FMDatabase *dataBase;

@end

@implementation DBHelper
// 单例模式
+ (DBHelper *)sharedInstance
{
    static DBHelper *sharedInstance = nil;
    static dispatch_once_t predicate;
    dispatch_once(&predicate, ^{
        sharedInstance = [[DBHelper alloc] init];
    });
    return sharedInstance;
}

///判断字符串是否为空
- (BOOL) isBlankString:(NSString *)string
{
    if (string == nil || string == NULL) {
        return YES;
    }
    if ([string isKindOfClass:[NSNull class]]) {
        return YES;
    }
    if ([[string stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]] length] == 0) {
        return YES;
    }
    if( [string isEqualToString:@""])
    {
        return YES;
    }
    if([string isEqualToString:@"<null>"])
    {
        return YES;
    }
    if([string isEqualToString:@"(null)"])
    {
        return YES;
    }
    return NO;
}

///查询数据库，如果数据库不存在新建一个
-(void)CreateMyServer :(NSString *)ServerName mydbName:(NSString *)mydbName
{
    if (ServerName.length == 0) {
        return;
    }
    //Documents:
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES);
    
    //username md5
    const char *cStr = [ServerName UTF8String];
    unsigned char result[16];
    CC_MD5(cStr, (CC_LONG)strlen(cStr), result);
    NSString* MD5 =  [NSString stringWithFormat:@"%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x%02x",result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7],result[8], result[9], result[10], result[11],result[12], result[13], result[14], result[15]];
    
    //数据库文件夹
    NSString * documentsDirectory = [[paths objectAtIndex:0] stringByAppendingPathComponent:MD5];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL isDir = FALSE;
    BOOL isDirExist = [fileManager fileExistsAtPath:documentsDirectory isDirectory:&isDir];
    if(!(isDirExist && isDir)) {
        BOOL bCreateDir = [fileManager createDirectoryAtPath:documentsDirectory withIntermediateDirectories:YES attributes:nil error:nil];
        if(!bCreateDir) {
            NSLog(@"Create Database Directory Failed.");
        }
        NSLog(@"%@", documentsDirectory);
    }
    
    NSString *dbPath = [documentsDirectory stringByAppendingPathComponent:mydbName];
    
    //判断数据库是否已存在
    if (self.dataBase) {
        [self.dataBase close];
        self.dataBase = nil;
    }
    //新建一个数据库
    self.dataBase = [FMDatabase databaseWithPath:dbPath];
    [self.dataBase open];
    //如果数据库为null，说明没有建成功
    if(!self.dataBase)
    {
        printf("\n\n========打开数据库失败");
    }else
    {
        printf("\n\n========打开数据库成功");
    }
    ///创建个人信息表
    [self userInfoTableCreate];
}


/// 判断指定表是否存在
- (BOOL)checkTableExist:(NSString *)tableName {
    BOOL result = NO;
    NSString* lowtableName = [tableName lowercaseString];
    
    FMResultSet *rs = [self.dataBase executeQuery:@"SELECT [sql] FROM sqlite_master WHERE [type] = 'table' AND lower(name) = ?", lowtableName];
    result = [rs next];
    [rs close];
    
    return result;
}

/**
 *  创建个人信息表
 *  userid 用户ID
 *  nickName 用户昵称
 *  sex  用户性别
 *  department 用户部门
 */
- (void)userInfoTableCreate 
{
    //如果个人信息表不存在新建个人信息表
    if(![self checkTableExist:@"userInfo"])
    {
        [self createTable:@"userInfo" sql:@"CREATE table userInfo (id TEXT NOT NULL PRIMARY KEY UNIQUE ON CONFLICT REPLACE, userId TEXT, nickName TEXT, sex INTEGER, department TEXT)"];
    }
}

/**
 *  查询用户昵称
 */
- (NSString*)getUserName:(NSString*)userId
{
    NSString *stringname = nil;
    
    FMResultSet *rs = [self.dataBase executeQuery:@"SELECT nickName FROM userInfo WHERE userid=?", userId];
    if ([rs next]) {
        stringname = [rs stringForColumnIndex:0];
    }
    [rs close];
    
    return stringname;
}

/**
 *  查用户部门
 *  入参：userId 用户ID
 *  出参：department 用户部门名称
 */
-(NSString *)GetdepartmentName :(NSString *)userId
{
    NSString *departmentName = nil;
    
    FMResultSet *DeName = [self.dataBase executeQuery:@"SELECT department FROM userInfo WHERE userid=?", userId];
    if ([DeName next]) {
        departmentName = [DeName stringForColumnIndex:0];
    }
    [DeName close];
    
    return departmentName;
}

/**
 *   插入数据
 *   表名默认为userInfo
 *   nickName 用户昵称
 *   userID 用户ID 此处和数据库中的ID有区别
 *   sex  用户性别
 *   department  用户部门
 *   infoID 表索引，默认为1000
 */
- (void)inserttableName:(NSString *)tableName  nickName:(NSString *)nickName infoID:(NSString *)infoID  userID:(NSString *)userId sex:(NSString *)sex department:(NSString *)department
{
    BOOL insert = [self.dataBase executeUpdate:@"INSERT INTO userInfo (id,userid,nickName,sex,department) values(?,?,?,?,?)",infoID,userId,nickName,sex,department];
    if (insert) {
        printf("\ninsert the data success");
    }else{
        printf("\ninsert the data error");
    }
}

// 创建表
- (void) createTable:(NSString*)tableName sql:(NSString *)createSql {
    
    BOOL isExist = [self.dataBase tableExists:tableName];
    if (!isExist) {
        [self.dataBase executeUpdate:createSql];
        printf("\n\n\ncreate the table success");
    }else
    {
        printf("\n\n\ncreate the table error");
    }
}

/**
 * 增加表字段
 * TableName 表名
 * UpdateStr 要插入的字段
 */
-(void)AddcodeforTableView :(NSString *)tableName addcodeStr:(NSString *)addcodeStr
{
    if (![self.dataBase columnExists:addcodeStr inTableWithName:tableName])
    {
        NSString *addStr = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ TEXT",tableName,addcodeStr];
        
        BOOL addsecuess = [self.dataBase executeUpdate:addStr];
        if(addsecuess == YES)
        {
            printf("\n\n\nupdate the table data success");
            
        }else
        {
            printf("\n\n\nupdate the table data error");
        }
    }
}

/**
 *  查询表里面的数据
 *  tableName 表名
 *  userid 要查询用户的ID
 *  knowStr 根据什么来查询，此处默认是用户ID---->userid
 *  InquireName 查询的参数
 *  ReturnName 返回的参数
 */
-(NSString *)InquiretableName :(NSString *)tableName InquireName:(NSString *)InquireName knowStr:(NSString *)knowStr userid:(NSString *)userid
{
    NSString *ReturnName = [[NSString alloc]init];
    if([self isBlankString:knowStr])
    {
        knowStr = @"id";
    }
    
    NSString *sql = [NSString stringWithFormat:@"SELECT %@ FROM %@ WHERE %@ = '%@'",InquireName,tableName,knowStr,userid];
    FMResultSet *rs = [self.dataBase executeQuery:sql];
    if ([rs next]){
        ReturnName = [rs stringForColumnIndex:0];
        printf("\n\n\n查询个人信息成功\n");
        
    }else
    {
        printf("\n\n\n查询个人信息失败\n");
    }
    [rs close];

    return ReturnName;
}

/**
 *  查询表里面的所有数据
 *  tableName 表名
 */
- (NSArray *)loadalldata:(NSString *)tableName
{
    NSString *sqlstr = [NSString stringWithFormat:@"SELECT * FROM %@",tableName];
    FMResultSet *result =  [self.dataBase executeQuery:sqlstr];
    NSMutableArray *infotablist = [NSMutableArray array];
    // 从结果集里面往下找
    while ([result next]) {
        infoModel *inModel = [[infoModel alloc]init];
        inModel.ID = [result stringForColumn:@"id"];
        inModel.userid = [result stringForColumn:@"userid"];
        inModel.nickName = [result stringForColumn:@"nickName"];
        inModel.sex = [result stringForColumn:@"sex"];
        inModel.department = [result stringForColumn:@"department"];
        inModel.userimg = [result stringForColumn:@"userimg"];
        [infotablist addObject:inModel];
    }
    
     return [infotablist copy];
}
/**
 *  删除指定表
 *  tableName 表名
 */
- (BOOL)deleteWithTable:(NSString*)tableName
{
    return [self runSql:[NSString stringWithFormat:@"DELETE FROM %@",tableName]];
}
- (BOOL)runSql:(NSString*)sql
{
    return [self.dataBase executeUpdate:sql];
}

/**
 *  修改表中的数据
 */
-(BOOL)executeUpdatedatafromtable :(NSString *)tableName updateStr:(NSString *)updateStr NewStr:(NSString *)NewStr userId:(NSString *)userId
{
    NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE id = '%@'",tableName,updateStr,NewStr,userId];
    
    return  [self.dataBase executeUpdate:sql];
}

///查询指定行数据
-(NSDictionary *)getcellrowdataTableName :(NSString *)tableName cellID:(NSString *)cellID
{
    NSDictionary *retDic = [[NSDictionary alloc]init];
    NSString *sqlstr = [NSString stringWithFormat:@"SELECT %@ FROM %@",cellID,tableName];
    FMResultSet *result =  [self.dataBase executeQuery:sqlstr];
    // 从结果集里面往下找
    if([result next])
    {
//        retDic = [result stringForColumnIndex:0];
    }
    return retDic;
}

@end
